﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using CommunitySystem.BO;
using CommunitySystemModel;

namespace CommunitySystem
{
    public class KaoHeBO
    {
        public ObservableCollection<KaoHeModel> GetKaoHe(string OrgIDs)
        {
            ObservableCollection<KaoHeModel> rights = new ObservableCollection<KaoHeModel>();
            string sql = "";
            sql = string.Format(@"select k.id,emp.name,emp.id EmpbasicID,o.name OrgName,o.id OrgID,emp.PositionName OldKeShi,DutyNo OldZhiWei,DutyAttribute ZhiWuShuXing,
DutyLevel ZhiWuCengCi,BRZDQRQ RZQMSJ,k.status,k.nianshu,k.RenZhiStatus,k.begindate,k.enddate,k.HuiYiDate,d.JobDate,d.RXRZWSXSJ,k.NewZhiWuShuXing
      ,k.KeShi
      ,k.ZhiWei
      ,k.OrgID
      ,k.NianShu,k.TotalCount,k.Realcount,k.YouXiu,k.ChenZhi,k.BuChenZhi,k.QiQuan,k.UnAgreeRate from kaohe k inner join dbo.Emp_BasicInfo emp on k.empbasicid = emp.id
inner join orgforhr o on o.id = emp.orgid
inner join dbo.Emp_Duty d on d.empbasicid = emp.id and JobStatus = '在任'");
            if (!string.IsNullOrEmpty(OrgIDs) && OrgIDs != "0")
                sql += " where o.id in (" + OrgIDs + ")";

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                KaoHeModel khm = new KaoHeModel();
                khm = KaoHeModel.FillEntity(dr);
                ObservableCollection<KaoHe_AttatchmentModel> temp = GetKaoHe_Attatchment(Convert.ToInt32(dr["ID"]));
                if (temp != null && temp.Count > 0)
                {
                    foreach (KaoHe_AttatchmentModel i in temp)
                    {
                        if (!khm.Attachment.Contains(i))
                            khm.Attachment.Add(i);
                    }
                }
                rights.Add(khm);
            }
            return rights;
        }

        public ObservableCollection<KaoHe_AttatchmentModel> GetKaoHe_Attatchment(int id)
        {
            ObservableCollection<KaoHe_AttatchmentModel> rights = new ObservableCollection<KaoHe_AttatchmentModel>();
            string sql = "";
            sql = string.Format(@"select * from KaoHe_Attatchment where mainid = {0}", id);

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(KaoHe_AttatchmentModel.FillEntity(dr));
            }
            return rights;
        }

        public bool AddEmp_Duty(KaoHeModel item)
        {
            string poge = "";
            if(item.RenZhiStatus == "连任") poge = "是";
            if(item.RenZhiStatus == "转任") poge = "否";
            string sql = "";
            //            if(item.ID == 0)
            //            {
            //                sql = string.Format(@"INSERT INTO KaoHe
            //           ([EmpbasicID]
            //           ,[Status]
            //           ,[RenZhiStatus]
            //           ,[KeShi]
            //           ,[ZhiWei]
            //           ,[OrgID]
            //           ,[NianShu])
            //     VALUES
            //           ('{0}'
            //           ,'{1}'
            //           ,'{2}'
            //           ,'{3}'
            //           ,'{4}'
            //           ,'{5}'
            //           ,'{6}'
            //          );select @@IDENTITY;", item.EmpbasicID, item.Status, item.RenZhiStatus, item.KeShi, item.ZhiWei, item.OrgID, item.NianShu);
            //                DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            //                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            //                {
            //                    int id = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            //                    if(item.Attachment != null && item.Attachment.Count > 0)
            //                    {
            //                        foreach(KaoHe_AttatchmentModel i in item.Attachment)
            //                        {
            //                            string sql1 = string.Format(@"INSERT INTO KaoHe_Attatchment
            //           ([MainID]
            //           ,[Key]
            //           ,[filename]
            //           ,[realname])
            //     VALUES
            //           ({0}
            //           ,'{1}'
            //           ,'{2}'
            //           ,'{3}')",id,i.Key,i.filename,i.realname);
            //                            if (!string.IsNullOrEmpty(sql1))
            //                                AccessHelper.GetInstance().ExecuteNonQuery(sql1);
            //                        }

            //                    }
            //                }
            //            }
            //            else
            //            {
            sql =
                string.Format(
                    @"UPDATE KaoHe
   SET [EmpbasicID] = {0}
      ,[Status] = '{1}'
      ,[RenZhiStatus] = '{2}'
      ,[KeShi] =  '{3}'
      ,[ZhiWei] =  '{4}'
      ,[OrgID] =  '{5}'
      ,[NianShu] =  '{6}',[TotalCount] = '{7}'
      ,[Realcount] = '{8}'
      ,[YouXiu] =  '{9}'
      ,[ChenZhi] =  '{10}'
      ,[BuChenZhi] =  '{11}'
      ,[QiQuan] =  '{12}',[UnAgreeRate] =  {13},begindate='{14}',enddate = '{15}',HuiYiDate = '{16}',NewZhiWuShuXing = '{17}'
 WHERE ID = {18}",
                    item.EmpbasicID, item.Status, item.RenZhiStatus, item.KeShi, item.ZhiWei, item.OrgID, item.NianShu, item.TotalCount,
                        item.Realcount, item.YouXiu, item.ChenZhi, item.BuChenZhi, item.QiQuan, item.UnAgreeRate, item.Begindate, item.Enddate, item.HuiYiDate,item.NewZhiWuShuXing, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            //if (item.Attachment != null && item.Attachment.Count > 0)
            //{
            string sql2 = string.Format(@"delete from KaoHe_Attatchment where mainid = {0}", item.ID);
            if (!string.IsNullOrEmpty(sql2))
                AccessHelper.GetInstance().ExecuteNonQuery(sql2);
            foreach (KaoHe_AttatchmentModel i in item.Attachment)
            {
                string sql1 = string.Format(@"INSERT INTO KaoHe_Attatchment
           ([MainID]
           ,[Key]
           ,[filename]
           ,[realname])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}')", item.ID, i.Key, i.filename, i.realname);
                if (!string.IsNullOrEmpty(sql1))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql1);
            }

            //}
            //}
            if (item.NewZhiWuShuXing != item.ZhiWuShuXing) item.RXRZWSXSJ = DateTime.Today;
            if (item.NewZhiWuShuXing == "领导职务" && item.RenZhiStatus == "转任") item.ServiceMode = "转任（领导职务）";
            if (item.NewZhiWuShuXing == "非领导职务" && item.RenZhiStatus == "转任") item.ServiceMode = "转任（非领导职务）";
            if(item.RenZhiStatus == "连任")
            {
                string sql3 = string.Format(@"update Emp_Duty set JobStatus = '不在任',iskaohe = '0' where empbasicid = {0};INSERT INTO Emp_Duty
           ([EmpbasicID]
           ,[DutyNo]
           ,[DutyName]
           ,[OKDate]
           ,[JobStatus],ServiceMode,JobDate,RXRZWSXSJ,DutyLevel,DutyAttribute,LianRen,BRZDQRQ)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'在任'
           ,'{4}' ,'{5}','{6}','{7}','{8}','1','{9}');", item.EmpbasicID, item.ZhiWei, item.KeShi, item.NewZhiWuShuXing == "非领导职务" ? item.HuiYiDate : item.Begindate, item.ServiceMode, item.JobDate, item.RXRZWSXSJ,
                                            item.ZhiWuCengCi, item.NewZhiWuShuXing,item.Enddate);
                if (!string.IsNullOrEmpty(sql3))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql3);
            }
            if (item.RenZhiStatus == "转任")
            {
                string sql3 = string.Format(@"update Emp_Duty set JobStatus = '不在任',iskaohe = '0' where empbasicid = {0};
update Emp_Resume set enddate =GETDATE()  where id = 
                                 (select top 1 id from dbo.Emp_Resume where empbasicid = {0} order by begindate desc);
INSERT INTO Emp_Duty
           ([EmpbasicID]
           ,[DutyNo]
           ,[DutyName]
           ,[OKDate]
           ,[JobStatus],ServiceMode,JobDate,RXRZWSXSJ,DutyLevel,DutyAttribute,BRZDQRQ)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'在任'
           ,'{4}' ,'{5}','{6}','{7}','{8}','{11}');INSERT INTO Emp_Resume
           ([EmpbasicID]
           ,[BeginDate]
           ,[Unit]
           ,[Duty]) values ({0},'{3}','{9}','{10}')", item.EmpbasicID, item.ZhiWei, item.KeShi, item.NewZhiWuShuXing == "非领导职务" ? item.HuiYiDate : item.Begindate, item.ServiceMode, item.JobDate, item.RXRZWSXSJ,
                                            item.ZhiWuCengCi, item.NewZhiWuShuXing, item.OrgName, item.KeShi + item.ZhiWei, item.Enddate);
                if (!string.IsNullOrEmpty(sql3))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql3);
            }
            if (item.RenZhiStatus == "暂不任岗位")
            {
                string sql3 = string.Format(@"update Emp_Duty set iskaohe = '0',KaoHeStatus = '1' where empbasicid = {0};
update Emp_Resume set enddate =GETDATE()  where id = 
                                 (select top 1 id from dbo.Emp_Resume where empbasicid = {0} order by begindate desc);
delete from BianZhiJieYongEmp where EmpBasicID = {0}", item.EmpbasicID, item.ZhiWei, item.KeShi, item.NewZhiWuShuXing == "非领导职务" ? item.HuiYiDate : item.Begindate, item.ServiceMode, item.JobDate, item.RXRZWSXSJ,
                                            item.ZhiWuCengCi, item.NewZhiWuShuXing, item.OrgName, item.KeShi + item.ZhiWei, item.Enddate);
                if (!string.IsNullOrEmpty(sql3))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql3);
            }
            return true;
        }

        public bool InsertKaoHe(ObservableCollection<int> empids)
        {
            foreach(int i in empids)
            {
                string sql = string.Format("insert into kaohe (EmpbasicID) values ({0});update Emp_Duty set iskaohe = 1 where empbasicid = {0} and JobStatus = '在任'", i);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            return true;
        }

        #region  试用期满考核
        public ObservableCollection<Emp_SYQMKHModel> GetSYQMKH(string con, int index, int pagesize)
        {
            ObservableCollection<Emp_SYQMKHModel> rights = new ObservableCollection<Emp_SYQMKHModel>();
            string sql = "";
            sql = string.Format(@"select top({0}) ROW_NUMBER() over(order by ID desc) as Sequence,IsSelected='false'
                                ,* from
                                (
                                select ROW_NUMBER() over(order by ID desc) as rowsID,* from Emp_SYQMKH emp where 1=1 {2}
                                ) a where a.rowsID>=
                                (
                                SELECT ISNULL(MAX(rowsID),0) FROM 
                                (
                                select top({0}*({1}-1)+1) * from
                                (
                                select ROW_NUMBER() over(order by ID desc) as rowsID,* from Emp_SYQMKH emp where 1=1 {2}
                                )b
                                )c
                                ) order by OrgID", pagesize, index, con);
         

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Emp_SYQMKHModel khm = new Emp_SYQMKHModel();
                khm = Emp_SYQMKHModel.FillEntity(dr);
                ObservableCollection<Emp_SYQMKHFJModel> temp = GetFJ(Convert.ToInt32(dr["ID"]));
                if (temp != null && temp.Count > 0)
                {
                    foreach (Emp_SYQMKHFJModel i in temp)
                    {
                        if (!khm.Attachment.Contains(i))
                            khm.Attachment.Add(i);
                    }
                }
                rights.Add(khm);
            }
            return rights;
        }
        public int GetSYQMKHCount(string con)
        {
            string sql =string.Format( @"select  COUNT(id) from Emp_SYQMKH emp where 1=1 {0} ",con);
            if (!string.IsNullOrEmpty(con))
            {
                sql += con;
            }

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
        }
        public ObservableCollection<Emp_SYQMKHFJModel> GetFJ(int id)
        {
            ObservableCollection<Emp_SYQMKHFJModel> rights = new ObservableCollection<Emp_SYQMKHFJModel>();
            string sql = "";
            sql = string.Format(@"select * from Emp_SYQMKHFJ where mainid = {0}", id);

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_SYQMKHFJModel.FillEntity(dr));
            }
            return rights;
        }

        public bool SYQMKH_SB(Emp_SYQMKHModel model)
        {
            bool result = false;
            string sql = string.Format(@"delete Emp_SYQMKHFJ where MainID={17};UPDATE Emp_SYQMKH
   SET [EID] ={0},[Name] ='{1}',[CardNo] ='{2}',[TLSJ] = '{3}',[KHStatus] = '{4}',[Status] = '{5}',[REbeDate] = '{6}'
      ,[DutyLevel] = '{7}',[KS] = '{8}',[DutyNo] = '{9}' ,[BHGYY] ='{10}',[SYQYSYY] = '{11}',[SYQYSDate] = '{12}'
      ,[BZ] = '{13}',[OrgID] = {14},[OrgName] = '{15}',ISZS='{16}'
 WHERE ID={17}", model.EID, model.Name, model.CardNo, model.TLSJ, model.KHStatus, model.Status, model.REbeDate
               , model.DutyLevel, model.KS, model.DutyNo, model.BHGYY, model.SYQYSYY, model.SYQYSDate
               , model.BZ,model.OrgID,model.OrgName,model.ISZS,model.ID);
            AccessHelper.GetInstance().ExecuteNonQuery(sql);
            foreach (Emp_SYQMKHFJModel i in model.Attachment)
            {
                string sql1 = string.Format(@" INSERT INTO Emp_SYQMKHFJ
           ([MainID]
           ,[Key]
           ,[filename]
           ,[realname],Status,FileSize,Percentage,Type)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}','Finished','{4}','100','{5}')", model.ID, i.Key, i.filename, i.realname,i.FileSize,i.Type);
                if (!string.IsNullOrEmpty(sql1))
                    if (AccessHelper.GetInstance().ExecuteNonQuery(sql1) > 0)
                        result = true;
            }

            return result;
        }

        public bool SYQMKH_SH(string Ids, string bz,string zt)
        {
            string sql=string.Empty;
            if(string.IsNullOrEmpty(bz))
                sql = string.Format("update emp_syqmkh set Status='{0}',BZ=KHStatus where ID in ({1})", zt,Ids);
            else  sql = string.Format("update emp_syqmkh set Status='{0}',BZ='{1}' where ID in ({2})",zt,bz,Ids);

            if (AccessHelper.GetInstance().ExecuteNonQuery(sql) > 0)
            {

                //审核通过
                string sql_sh = string.Empty;
                if (zt == "审核通过")
                {
                    #region  基本信息：
                    //人员编制类型：人员编制类型目前有三种情况，党群系统、行政系统、地区系统在任除科室名称为司法所外所有人员自动设定为：行政。
                    //地区系统在任科室名称为司法所的、司法系统设定为：政法行政。
                    //参公系统设定为：参照管理事业。
                    //户籍所在地：上海
                    //个人身份：党群、行政、政法、地区系统为：公务员；参公为：参照公务员法管理事业单位工作人员
                    //人员类别：参公系统为：参照管理的事业工作人员
                    //          徐汇区科学技术协会、共青团徐汇区委员会机关、徐汇区残疾人联合会、徐汇区妇女联合会、徐汇区红十字会设定为：参照管理的机关工作人员
                    //           其他单位设定位：综合管理类公务员
                    //公务员登记时间：取考核界面任职定级 自**** 那个时间，一般为默认的当年7月1日

                    #endregion
                    #region  基本信息sql
                    //人员编制类型目前有三种情况，党群系统、行政系统、地区系统在任除科室名称为司法所外所有人员自动设定为：行政。
                    sql_sh += string.Format(@"update Emp_BasicInfo set OrganizationType='行政'
                                    where ID in(
                                    select EID from emp_syqmkh where Status='审核通过'  and KHStatus='合格' and KS!='司法所' 
                                    and OrgID in (select ID from OrgForHR where type in ('党群系统','行政系统','地区系统')) and id in ({0})
                                    );", Ids);
                    //地区系统在任科室名称为司法所的、司法系统设定为：政法行政。
                    sql_sh += string.Format(@"update Emp_BasicInfo set OrganizationType='政法行政'
                                    where ID in(
                                    select EID from emp_syqmkh where Status='审核通过'  and KHStatus='合格' and KS in ('司法所','司法系统') 
                                    and OrgID in (select ID from OrgForHR where type in ('地区系统')) and id in ({0})
                                    );", Ids);
                    //参公系统设定为：参照管理事业。
                    sql_sh += string.Format(@"update Emp_BasicInfo set OrganizationType='参照管理事业'
                                    where ID in(
                                    select EID from emp_syqmkh where Status='审核通过' and KHStatus='合格'
                                    and OrgID in (select ID from OrgForHR where type in ('参公系统')) and id in ({0})
                                    );", Ids);

                    //户籍所在地 ：上海HouseholdRegister
                    sql_sh += string.Format(@"update Emp_BasicInfo set HouseholdRegister='上海'
                                    where ID in(
                                    select EID from emp_syqmkh where Status='审核通过'  and KHStatus='合格' and id in ({0})
                                    );", Ids);
                    //个人身份 EmpIdentity：党群、行政、政法、地区系统为：公务员；
                    sql_sh += string.Format(@"update Emp_BasicInfo set EmpIdentity='公务员'
                                    where ID in(
                                    select EID from emp_syqmkh where Status='审核通过'   and KHStatus='合格'
                                    and OrgID in (select ID from OrgForHR where type in ('党群系统','行政系统','政法系统','地区系统')) and id in ({0})
                                    );", Ids);
                    //个人身份 EmpIdentity：参公为：参照公务员法管理事业单位工作人员
                    sql_sh += string.Format(@"update Emp_BasicInfo set EmpIdentity='参照公务员法管理事业单位工作人员'
                                    where ID in(
                                    select EID from emp_syqmkh where Status='审核通过'  and KHStatus='合格' 
                                    and OrgID in (select ID from OrgForHR where type in ('参公系统')) and id in ({0})
                                    );", Ids);
                    //人员类别 PersonnelType：参公系统为：参照管理的事业工作人员
                    sql_sh += string.Format(@"update Emp_BasicInfo set PersonnelType='参照管理的事业工作人员'
                                    where ID in(
                                    select EID from emp_syqmkh where Status='审核通过'  and KHStatus='合格' 
                                    and OrgID in (select ID from OrgForHR where type in ('参公系统')) and id in ({0})
                                    );", Ids);
                    //          徐汇区科学技术协会、共青团徐汇区委员会机关、徐汇区残疾人联合会、徐汇区妇女联合会、徐汇区红十字会设定为：参照管理的机关工作人员
                    sql_sh += string.Format(@"update Emp_BasicInfo set PersonnelType='参照管理的机关工作人员'
                                    where ID in(
                                    select EID from emp_syqmkh where Status='审核通过'   and KHStatus='合格'
                                    and OrgID in (select ID from OrgForHR where Name in('徐汇区科学技术协会','共青团徐汇区委员会机关','上海市徐汇区残疾人联合会','徐汇区妇女联合会机关','徐汇区红十字会机关')) and id in ({0})
                                    );", Ids);
                    //           其他单位设定位：综合管理类公务员
                    sql_sh += string.Format(@"update Emp_BasicInfo set PersonnelType='综合管理类公务员'
                                    where ID in(
                                    select EID from emp_syqmkh where Status='审核通过'  and KHStatus='合格' 
                                    and OrgID in (select ID from OrgForHR where type not in ('参公系统') and Name not in('徐汇区科学技术协会','共青团徐汇区委员会机关','上海市徐汇区残疾人联合会','徐汇区妇女联合会机关','徐汇区红十字会机关')) and id in ({0})
                                    );", Ids);

                    //公务员登记时间 RegisterDate：取考核界面任职定级 自**** 那个时间，一般为默认的当年7月1日
                    sql_sh += string.Format(@"update Emp_BasicInfo set RegisterDate=c.REbeDate
                                from Emp_BasicInfo eb,(select EID,REbeDate from emp_syqmkh where Status='审核通过' and KHStatus='合格' and id in ({0})) c
                                where eb.ID=c.EID;", Ids);
                    AccessHelper.GetInstance().ExecuteNonQuery(sql_sh);
                    #endregion
                   
                    #region 职务信息  职务信息新增一条
                    //职务名称 DutyNo：取考核界面 职务代码 字段
                    //科室名称 DutyName：取考核界面 内设科室 字段
                    //职务层次 DutyLevel：取考核界面 职务层次 字段
                    //职务属性 DutyAttribute：统一为非领导职务
                    //批准时间 OKDate：取 自**** 时间，一般默认为当年7月1日
                    //任期：无
                    //任职机构名称 OrganizationName：取单位名称
                    //集体排序：无
                    //分管从事工作 WorkCharge：取 科室名称
                    //任职方式 ServiceMode：统一为 新录用公务员 
                    //破格提拔：无
                    //在职状态 JobStatus：在任 将上一条变为不在任
                    //任现职务层次时间 JobDate：取 批准时间一般默认为当年7月1日
                    //任现职务属性时间 RXRZWSXSJ：取 批准时间一般默认为当年7月1日
                    #endregion
                    #region 职务sql
                    sql_sh = string.Empty;
                    //在任变不在任
                    sql_sh += string.Format(@" update Emp_Duty set JobStatus='不在任'  where EmpbasicID in
                        (select EID from emp_syqmkh where Status='审核通过' and KHStatus='合格' and ID in ({0})) and	JobStatus='在任';", Ids);
                    //人员基本信息表中的科室 变
                    sql_sh += string.Format(@" update Emp_BasicInfo set PositionName=c.KS
                        from Emp_BasicInfo eb,(select EID,KS from emp_syqmkh where Status='审核通过' and KHStatus='合格' and id in ({0})) c
                        where eb.ID=c.EID ;", Ids);
                    AccessHelper.GetInstance().ExecuteNonQuery(sql_sh);
                    //插入新的职务
                    sql_sh = string.Empty;
                    sql_sh += string.Format(@" INSERT INTO Emp_Duty
                       ([EmpbasicID],[DutyNo],[DutyName],[DutyLevel],[DutyAttribute]
                       ,[OKDate],[OrganizationName],[WorkCharge],[ServiceMode]
                       ,[JobStatus],[JobDate],[RXRZWSXSJ])
                       select EID,DutyNo,KS,DutyLevel,'非领导职务'
                       ,REbeDate,OrgName,KS,'新录用公务员'
                       ,'在任',GETDATE(),GETDATE() from Emp_SYQMKH where Status='审核通过' and KHStatus='合格' and id in ({0});", Ids);
                    AccessHelper.GetInstance().ExecuteNonQuery(sql_sh);
                    #endregion

                    #region 简历信息：新增一条
                   //将上一条结束时间  EndDate 添加为 新增条目的开始时间
                   // 开始时间 BeginDate：取批准时间
                   // 结束时间 EndDate：空着
                   // 所在单位 Unit：取单位名称
                   // 担任职务 Duty：取 所在科室+职务代码 字段 
                   // 证明人：空着
                   // 是否为期间 IsPeriod：否

                    #endregion
                    #region  简历sql
                    sql_sh = string.Empty;
                    //变简历上条结束日期
                    sql_sh += string.Format(@" update Emp_Resume set EndDate=GETDATE()  where EmpbasicID in
                        (select EID from emp_syqmkh where Status='审核通过'  and KHStatus='合格' and ID in ({0})) and (EndDate is null or EndDate ='');", Ids);
                    AccessHelper.GetInstance().ExecuteNonQuery(sql_sh);
                    sql_sh = string.Empty;
                    //插入简历
                    sql_sh += string.Format(@" INSERT INTO Emp_Resume
                       ([EmpbasicID],[BeginDate],[EndDate],[Unit],[Duty],[IsPeriod])
                       select EID,GETDATE(),null,OrgName,(KS+' '+DutyNo),0 from Emp_SYQMKH where Status='审核通过' and KHStatus='合格' and id in ({0});", Ids);
                    AccessHelper.GetInstance().ExecuteNonQuery(sql_sh);
                    #endregion

                    #region  考核合格后，任职定级除科员外人员，需在车改表的变动表中增加信息
                    sql_sh = string.Empty;
                    sql_sh += string.Format(@" INSERT INTO CheGai
                           ([EmpID],[OldZhiJi],[Status],[Demo],[RelationID],keyid,type,CreateDate)
                      select EID,DutyLevel,'未导出','试用期满考核',id
                      ,KeyID=(case when (select count(cg.id) Cou from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
                      where cg.status = '未导出' and type='变动表' and emp.orgid = 
                      OrgID) > 0 then
                      (select top 1 keyid from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
                      where cg.status = '未导出' and type='变动表' and emp.orgid = 
                      OrgID) else
                      (select top 1 id + 1 from chegai order by id desc) end ),'变动表',GETDATE() from dbo.Emp_SYQMKH
                       where DutyLevel!='科员' and Status='审核通过' and KHStatus='合格' and ID in ({0});", Ids);
                    AccessHelper.GetInstance().ExecuteNonQuery(sql_sh);
                    #endregion
                }
                return true;
            }
            else return false;
        }
        #endregion
    }
}
